package yu.zhou.njupt.importdata.worker;

import java.io.File;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Properties;

import org.apache.commons.dbutils.DbUtils;
import org.dom4j.Document;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;

import yu.zhou.njupt.importdata.core.Importer;
import yu.zhou.njupt.importdata.core.impl.AccessImporter;
import yu.zhou.njupt.importdata.core.impl.AccessImporter2;
import yu.zhou.njupt.importdata.core.impl.CsvImporter;
import yu.zhou.njupt.importdata.core.impl.XlsImporter;
import yu.zhou.njupt.importdata.core.impl.XlsxImporter;
import yu.zhou.njupt.importdata.entity.ImportResult;
import yu.zhou.njupt.importdata.entity.enumtype.CreateTable;
import yu.zhou.njupt.importdata.entity.enumtype.DoTruncate;
import yu.zhou.njupt.importdata.entity.enumtype.NewImport;
import yu.zhou.njupt.importdata.util.DbUtil;

/**
 * @author yu.zhou
 *
 */
public class Worker {

    Properties props = null;
    Document procedureDoc = null;
    Connection conn = null;
    File sourceFilePath = null;
    Map<String, Importer> importerMap = null;
    List<String> unSupportedFileList = null;
    Map<String, ImportResult> successFileMap = null;
    List<String> failFileList = null;
    String dbName;
    DoTruncate doTruncate;
    NewImport newImport;
    CreateTable createTable;
    int titleNum;
    List<String> disableConsStrList = null; // 禁用约束的sql语句
    List<String> enableConsStrList = null; // 启用约束的sql语句
    List<String> disableIndexesStrList = null; // 删除索引的sql语句
    List<String> enableIndexesStrList = null; // 创建约束的sql语句
    
    public Worker(String dbName) throws Exception{
        this.dbName = dbName;
        doTruncate = DoTruncate.FALSE;
        newImport = NewImport.TRUE;
        init();
    }

    public Worker(String dbName, DoTruncate doTruncate, NewImport newImport, CreateTable createTable, int titleNum) throws Exception{
        this.dbName = dbName;
        this.doTruncate = doTruncate;
        this.newImport = newImport;
        this.titleNum = titleNum;
        this.createTable = createTable;
        init();
    }

    protected void init() throws Exception{
        props = new Properties();

        props.load(Worker.class.getClassLoader().getResourceAsStream("config.properties"));
        
        procedureDoc = new SAXReader().read(Worker.class.getClassLoader().getResourceAsStream("procedure.xml"));

        conn = DbUtil.getConnection(props, dbName);
        sourceFilePath = new File(props.getProperty(dbName + ".sourceFilePath"));
        
        importerMap = new HashMap<String, Importer>();
        importerMap.put("csv", new CsvImporter(conn, doTruncate, newImport, createTable, titleNum));
        importerMap.put("xls", new XlsImporter(conn, doTruncate, newImport, createTable, titleNum));
        importerMap.put("xlsx", new XlsxImporter(conn, doTruncate, newImport, createTable, titleNum));
        importerMap.put("mdb", new AccessImporter(conn, doTruncate, newImport, createTable, titleNum));
        importerMap.put("mdb2", new AccessImporter2(conn, doTruncate, newImport, createTable, titleNum));
        
        successFileMap = new HashMap<String, ImportResult>();
        failFileList = new ArrayList<String>();
        unSupportedFileList = new ArrayList<String>();
        
        disableConsStrList = new ArrayList<String>();
        enableConsStrList = new ArrayList<String>();
        disableIndexesStrList = new ArrayList<String>();
        enableIndexesStrList = new ArrayList<String>();
    }
    
    public void work() throws Exception {
        if (!sourceFilePath.exists()) {
            throw new Exception("指定的源文件目录不存在!!!!");
        } else {
        	importData(sourceFilePath);

        	/* 所有源文件的数据都导入成功，继续进行后续操作 */
        	if ((unSupportedFileList.size() == 0) && (failFileList.size() == 0)) {
        		rebuildIndexes(); // 重建索引
        		enableConstraints(); // 启用约束
        		gatherTableStatistics(); // 收集统计信息
        	} else {
        		System.out.println("重建索引语句:");
        		for (String str : enableIndexesStrList) {
        			System.out.println("  " + str + ";");
        		}

        		System.out.println("启用约束语句:");
        		if (enableConsStrList.size() != 0) {
        			String[] strings = new String[enableConsStrList.size()];
        			strings = enableConsStrList.toArray(new String[1]);
        			for (int i = strings.length - 1; i >= 0; i--) {
        				System.out.println("  " + strings[i] + ";");
        			}
        		}
            }
        }
        
        closeConnection();
    }
    
    /* 重建索引 */
    protected void rebuildIndexes() throws Exception {
        Statement s = conn.createStatement();
        String sql = null;
        
        if (enableIndexesStrList.size() > 0) {
            System.out.println(String.format("%-100s", "*").replace(' ', '*'));
            System.out.println("开始重建索引:");
            for (String command : enableIndexesStrList) {
                sql = command;
                try {
                    s.execute(sql);
                    System.out.println("  " + sql + ";");
                } catch (Exception e) {
                    System.out.println("  " + sql + "; --执行失败");
                }
            }
            System.out.println();
		}

        DbUtils.closeQuietly(s);
    }
    
    /* 索引创建好之后启用约束 */
    protected void enableConstraints() throws Exception {
        Statement s = conn.createStatement();
        String sql = null;
        
        if (enableConsStrList.size() > 0) {
        	System.out.println(String.format("%-100s", "*").replace(' ', '*'));
        	System.out.println("开始启用约束:");
        	String[] commands = enableConsStrList.toArray(new String[1]);
        	for (int i = commands.length - 1; i >= 0; i--) {
        		sql = commands[i];
        		try {
        			s.execute(sql);
        			System.out.println("  " + sql + ";");
        		} catch (Exception e) {
        			System.out.println("  " + sql + "; --执行失败");
        		}
        	}
        	System.out.println();
        }
        
		DbUtils.closeQuietly(s);
    }

    /* 数据导入完成后收集表的统计信息 */
	protected void gatherTableStatistics() throws Exception {
	    Element rootElement = procedureDoc.getRootElement();
	    Element element = (Element) rootElement.element("gather_stats");
	    String sql = element.getText();
	    CallableStatement cs = conn.prepareCall(sql);
	    cs.execute();
	}

	protected void importData(File file) {
		System.out.println(String.format("%-100s", "*").replace(' ', '*'));
        System.out.println("数据导入开始...");
        
        importFile(file);
        
        System.out.println("数据导入结束.");
        System.out.println();
        
        System.out.println(String.format("%-100s", "*").replace(' ', '*'));
        System.out.println("不支持文件：");
        for (String filename : unSupportedFileList) {
            System.out.println("    " + filename);
        }
        System.out.println();
        
        System.out.println("成功的文件：");
        for (Entry<String, ImportResult> entry : successFileMap.entrySet()) {
            System.out.println("    " + entry.getKey());
        }
        System.out.println();
        
        System.out.println("失败的文件：");
        for (String filename : failFileList) {
            System.out.println("    " + filename);
        }
        System.out.println();
    }
    
    /** 将源文件的数据导入到数据库的对应表中 */
    protected void importFile(File file) {
        for (File childFile : file.listFiles()) {
            if (childFile.isDirectory()) {
                importFile(childFile);
            } else {
                String filename = childFile.getName();
                System.out.println("  开始导入[" + childFile.getName() + "]...");
                ImportResult importResult = null;
                String fileTypeStr = "default";
                if (filename.lastIndexOf('.') != -1){
                    fileTypeStr = filename.substring(filename.lastIndexOf('.') + 1);
                };
                String lowerFileTypeStr = fileTypeStr.toLowerCase();
                
                if (importerMap.get(lowerFileTypeStr) != null) {
                    importResult = importerMap.get(lowerFileTypeStr).importData(childFile, 
                            disableConsStrList, enableConsStrList,
                            disableIndexesStrList, enableIndexesStrList);
                    if (importResult.isSuccess()) {
                        System.out.println("  [" + childFile.getName() + "]导入成功.");
                        successFileMap.put(childFile.getName(), importResult);
                    } else {
                        System.out.println("  [" + childFile.getName()+ "]导入失败!!!!");
                        failFileList.add(childFile.getName());
                    }
                } else {
                    System.out.println("  不支持该类型的文件");
                    unSupportedFileList.add(childFile.getName());
                }
            }
        }
    }

    protected void closeConnection() {
        DbUtils.closeQuietly(conn);
    }

}